Option Explicit

'Mise a jour de la feuille Synthese
Sub maj_synthese()
'Noms a ne pas changer : CHAPEAU
'Macro Dan - 07/06/17
Dim ligS As Byte
Dim ligC As Integer, ligD As Integer
Dim i As Integer
Dim cel
Dim firstaddress As String
Dim val1 As Currency, val2 As Currency, val3 As Currency, val11 As Currency, val21 As Currency, val31 As Currency
Dim FSynthese As Worksheet

Set FSynthese = Sheets("SYNTHESE")

Application.ScreenUpdating = False
With FSynthese 'Sheets("SYNTHESE")
    .Protect UserInterFaceOnly:=True
    ligS = .Cells.Find("CHAPEAU", LookIn:=xlValues).Row
End With
For i = 3 To 1 Step -1
    'Ajout ligne pour effacer donnees de ligne avant mise a jour de lafeuille Synthese  - 16/10/17 Dan
    With FSynthese
        Union(.Cells(ligS - i, 6), .Cells(ligS - i, 8), .Cells(ligS - i, 10)).ClearContents
    End With
    'Mise a jour feuille Synthese depuis feuille Chapeau ou Details
    With ThisWorkbook.Sheets("CHAPEAU").UsedRange
        Set cel = .Find(FSynthese.Range("C" & ligS - i), LookIn:=xlValues, LookAt:=xlPart)
        If Not cel Is Nothing Then
            firstaddress = cel.Address
            Do
                val1 = cel.Offset(0, 5).Value + val1
                val2 = cel.Offset(0, 8).Value + val2
                val3 = cel.Offset(0, 11).Value + val3
                Set cel = .FindNext(cel)
            Loop While Not cel Is Nothing And cel.Address <> firstaddress
            With FSynthese
                .Cells(ligS - i, 6) = val1: val11 = val11 + val1: val1 = 0
                .Cells(ligS - i, 8) = val2: val21 = val21 + val2: val2 = 0
                .Cells(ligS - i, 10) = val3: val31 = val31 + val3: val3 = 0
            End With
        End If
    End With
    If cel Is Nothing Then
        Select Case i
            Case Is = 2 'Cas ALEAS
                Dim DclD As Integer
                With Sheets("DETAILS")
                    ligD = .Range("B:B").Find(Range("C" & ligS - i), LookIn:=xlValues).Row
                    DclD = .Cells(6, .Columns.Count).End(xlToLeft).Column 'derniere colonne feuille DETAILS
                End With
                With FSynthese
                    .Cells(ligS - i, 6) = Sheets("DETAILS").Cells(ligD, 3)
                    .Cells(ligS - i, 8) = Sheets("DETAILS").Cells(ligD, 6)
                    .Cells(ligS - i, 10) = Sheets("DETAILS").Cells(ligD, DclD - 1)
                End With
            Case Is <> 2 'Cas Hors ALEAS et Hors FRAIS GENERAUX
                ligC = Sheets("CHAPEAU").Range("C:C").Find("CHAPEAU", LookIn:=xlValues).Row
                With FSynthese
                    .Cells(ligS - i, 6) = Sheets("CHAPEAU").Cells(ligC, 8) - val11
                    .Cells(ligS - i, 8) = Sheets("CHAPEAU").Cells(ligC, 11) - val21
                    .Cells(ligS - i, 10) = Sheets("CHAPEAU").Cells(ligC, 14) - val31
                End With
        End Select
    End If
Next
With FSynthese
    ligS = .Cells.Find("*COURANT*", LookIn:=xlValues).Row - 4
    Union(.Range(.Cells(ligS, 6), .Cells(ligS + 2, 6)), .Range(.Cells(ligS, 8), .Cells(ligS + 2, 8)), .Range(.Cells(ligS, 10), .Cells(ligS + 2, 10))) = 0 'Dan 13/12/17 - mise de 0 sur lignes 39 a 41
    Union(.Cells(ligS + 7, 6), .Cells(ligS + 7, 8), .Cells(ligS + 7, 10)) = 0 'Dan 13/12/17 - mise de 0 sur ligne 46
End With
End Sub

